package com.rp.service;

import cn.afterturn.easypoi.csv.CsvExportUtil;
import cn.afterturn.easypoi.csv.entity.CsvExportParams;
import cn.afterturn.easypoi.entity.ImageEntity;
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import cn.afterturn.easypoi.word.WordExportUtil;
import com.github.pagehelper.Page;
import com.github.pagehelper.PageHelper;
import com.opencsv.CSVWriter;
import com.rp.mapper.ResourceMapper;
import com.rp.mapper.UserMapper;
import com.rp.pojo.Resource;
import com.rp.pojo.User;
import com.rp.utils.ExcelExportEngine;
import jxl.demo.CSV;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.lucene.index.ParallelAtomicReader;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.util.Units;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xwpf.usermodel.*;
import org.slf4j.IMarkerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;
import org.springframework.util.ResourceUtils;
import org.springframework.web.multipart.MultipartFile;

import javax.imageio.ImageIO;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.OutputStreamWriter;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.function.IntPredicate;

//import jxl.Workbook;
//import org.apache.poi.ss.usermodel.Workbook;
//import jxl.Workbook;
//import jxl.write.Label;
//import jxl.write.WritableSheet;
//import jxl.write.WritableWorkbook;

@Service
@Slf4j
public class UserService {

    @Autowired
    private UserMapper userMapper;

    @Autowired
    private ResourceMapper resourceMapper;

    private static SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

    public List<User> findAll() {
        return userMapper.selectAll();
    }

    public List<User> findPage(Integer page, Integer pageSize) {
        PageHelper.startPage(page, pageSize);  //开启分页
        Page<User> userPage = (Page<User>) userMapper.selectAll(); //实现查询
        return userPage.getResult();
    }


    public void downLoadXlsByJxl(HttpServletResponse response) {
        try {
            //1 创建一个工作簿
            ServletOutputStream outputStream = response.getOutputStream();
            WritableWorkbook workbook = jxl.Workbook.createWorkbook(outputStream);

            //2.创建一个工作表
            WritableSheet sheet = workbook.createSheet("jxl入门sheet", 0);

            //2.1 设置列宽
            sheet.setColumnView(0, 5);
            sheet.setColumnView(1, 8);
            sheet.setColumnView(2, 15);
            sheet.setColumnView(3, 15);
            sheet.setColumnView(4, 30);

            //3.处理标题
            Label label = null;
            String[] titles = new String[]{"编号", "姓名", "手机号", "入职日期", "现住址"};
            for (int i = 0; i < titles.length; i++) {
                label = new Label(i, 0, titles[i]);
                sheet.addCell(label);
            }

            //4.处理导出内容
            List<User> users = this.findAll();

            int rowindex = 1;
            for (User user : users) {
                label = new Label(0, rowindex, user.getId().toString());
                sheet.addCell(label);

                label = new Label(1, rowindex, user.getUserName());
                sheet.addCell(label);

                label = new Label(2, rowindex, user.getPhone());
                sheet.addCell(label);

                label = new Label(3, rowindex, sdf.format(user.getHireDate()));
                sheet.addCell(label);

                label = new Label(4, rowindex, user.getAddress());
                sheet.addCell(label);

                rowindex++;

            }

            //导出文件的名称
            String filename = "第一个jxl入门.xls";
            //设置文件打开的方式和mime类型
            response.setHeader("Content-Disposition", "attachment;filename=" + new String(filename.getBytes(), "ISO8859-1"));
            response.setContentType("application/vnd.ms-excel");

            workbook.write();
            workbook.close();
            outputStream.close();
        } catch (Exception e) {

        }

    }

    public void uploadExcel(MultipartFile file) throws Exception {
        //根据上传的输入流创建workbook
        Workbook workbook = new XSSFWorkbook(file.getInputStream());

        // 获取工作簿中的第一个工作表
        Sheet sheet = workbook.getSheetAt(0);

        //获取这个工作表中的最后一行的索引 为了循环遍历使用
        int lastRowIndex = sheet.getLastRowNum();

        for (int i = 1; i < lastRowIndex; i++) {
            Row row = sheet.getRow(i);
            String userName = row.getCell(0).getStringCellValue(); //用户名

            String phone = null;
            try {
                phone = row.getCell(1).getStringCellValue();  //手机号
            } catch (Exception e) {
                phone = row.getCell(1).getNumericCellValue() + "";  //手机号
            }
            String province = row.getCell(2).getStringCellValue(); //省

            String city = row.getCell(3).getStringCellValue(); //市

            int salary = ((Double) row.getCell(4).getNumericCellValue()).intValue(); //工资

            String hireDateStr = row.getCell(5).getStringCellValue(); //入职日期
            Date hireDate = sdf.parse(hireDateStr);

            String birthdayStr = row.getCell(6).getStringCellValue(); //出生日期
            Date birthday = sdf.parse(birthdayStr);

            String address = row.getCell(7).getStringCellValue(); //现住址


            //构建对象
            User user = new User();
            user.setUserName(userName);
            user.setPhone(phone);
            user.setProvince(province);
            user.setCity(city);
            user.setSalary(salary);
            user.setHireDate(hireDate);
            user.setBirthday(birthday);
            user.setAddress(address);

            userMapper.insert(user);

        }

    }

    public void downLoadXlsxByPoi(HttpServletResponse response) throws Exception {

        //创建一个空的工作簿
        Workbook workbook = new XSSFWorkbook();

        //创建一个工作表
        Sheet sheet = workbook.createSheet("测试");

        //设置列宽
        sheet.setColumnWidth(0, 5 * 256);
        sheet.setColumnWidth(1, 8 * 256);
        sheet.setColumnWidth(2, 15 * 256);
        sheet.setColumnWidth(3, 15 * 256);
        sheet.setColumnWidth(4, 30 * 256);


        //处理标题
        String[] titles = new String[]{"编号", "姓名", "手机号", "入职日期", "现住址"};
        //创建标题行
        Row titleRow = sheet.createRow(0);

        Cell cell = null;
        for (int i = 0; i < titles.length; i++) {
            cell = titleRow.createCell(i);
            cell.setCellValue(titles[i]);
        }

        List<User> all = this.findAll();

        int rowIndex = 1;
        Row row = null;
        for (User user : all) {
            row = sheet.createRow(rowIndex);
            cell = row.createCell(0);
            cell.setCellValue(user.getId());

            cell = row.createCell(1);
            cell.setCellValue(user.getUserName());

            cell = row.createCell(2);
            cell.setCellValue(user.getPhone());

            cell = row.createCell(3);
            cell.setCellValue(sdf.format(user.getHireDate()));

            cell = row.createCell(4);
            cell.setCellValue(user.getAddress());

            rowIndex++;
        }


        //导出数据
        String filename = "员工数据表.xlsx";

        //设置打开方式和mime类型
        response.setHeader("Content-Disposition", "attachment;filename=" + new String(filename.getBytes(), "ISO8859-1"));
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");

        ServletOutputStream outputStream = response.getOutputStream();
        workbook.write(outputStream);
        workbook.close();
        outputStream.close();
    }

    public void downLoadXlsxByPoiWithStyle(HttpServletResponse response) throws Exception {

        //1.创建一个全新的 里面什么都没有的 工作簿
        Workbook workbook = new XSSFWorkbook();
        //2 创建一个全新的工作表
        Sheet sheet = workbook.createSheet("带有样式高版本poi");
        //设置表格宽度
        sheet.setColumnWidth(0, 5 * 256);
        sheet.setColumnWidth(1, 8 * 256);
        sheet.setColumnWidth(2, 15 * 256);
        sheet.setColumnWidth(3, 15 * 256);
        sheet.setColumnWidth(4, 30 * 256);


        //1.1 设置单元格样式:
        //需求1.边框线:全边框  2.行高:42  3.合并单元格 : 第一行的第一个单元格到第五个单元格
        //对齐方式: 水平垂直都要居中
        //字体:黑体18号
        CellStyle bigTitleStyle = workbook.createCellStyle();
        //设置边框
        bigTitleStyle.setBorderBottom(BorderStyle.THIN);
        bigTitleStyle.setBorderTop(BorderStyle.THIN);
        bigTitleStyle.setBorderLeft(BorderStyle.THIN);
        bigTitleStyle.setBorderRight(BorderStyle.THIN);

        //设置对齐放回寺
        bigTitleStyle.setAlignment(HorizontalAlignment.CENTER);//水平
        bigTitleStyle.setVerticalAlignment(VerticalAlignment.CENTER);//居中

        //设置字体颜色
        Font font = workbook.createFont();
        font.setFontName("黑体");
        font.setFontHeightInPoints((short) 18);
        bigTitleStyle.setFont(font);

        //3 创建了第一行
        Row bigtitleRow = sheet.createRow(0);
        //设置行高
        bigtitleRow.setHeightInPoints(42);

        for (int i = 0; i < 5; i++) {
            Cell cell = bigtitleRow.createCell(i);
            cell.setCellStyle(bigTitleStyle);
        }

        //合并单元格
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 4)); //int firstRow, int lastRow, int firstCol, int lastCol

        //向单元格中设置值
        sheet.getRow(0).getCell(0).setCellValue("用户信息数据");

        //=========================设置大标题结束  ,  设置小标题开始================================================


        CellStyle litleCellStyle = workbook.createCellStyle();
        litleCellStyle.cloneStyleFrom(bigTitleStyle);
        //设置字体颜色
        Font litlefont = workbook.createFont();
        litlefont.setFontName("宋体");
        litlefont.setFontHeightInPoints((short) 12);
        litlefont.setBold(true);//加粗
        litleCellStyle.setFont(litlefont);

        Row titleRow = sheet.createRow(1);
        titleRow.setHeightInPoints(30);
        String[] titles = new String[]{"编号", "姓名", "手机号", "入职日期", "现住址"};

        for (int i = 0; i < titles.length; i++) {
            Cell cell = titleRow.createCell(i);
            cell.setCellStyle(litleCellStyle);
            cell.setCellValue(titles[i]);
        }
        //=========================设置小标题结束  ,  设置内容开始================================================


        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.cloneStyleFrom(bigTitleStyle);
        cellStyle.setAlignment(HorizontalAlignment.LEFT);//向左靠齐
        //设置字体颜色
        Font contentFont = workbook.createFont();
        contentFont.setFontName("宋体");
        contentFont.setFontHeightInPoints((short) 11);
        contentFont.setBold(false);//加粗
        cellStyle.setFont(litlefont);


        List<User> all = this.findAll();

        int rowIndex = 2;
        Row contentRow = null;
        Cell concontCell = null;
        for (User user : all) {
            contentRow = sheet.createRow(rowIndex);
            concontCell = contentRow.createCell(0);
            concontCell.setCellValue(user.getId());
            concontCell.setCellStyle(cellStyle);

            concontCell = contentRow.createCell(1);
            concontCell.setCellValue(user.getUserName());
            concontCell.setCellStyle(cellStyle);

            concontCell = contentRow.createCell(2);
            concontCell.setCellValue(user.getPhone());
            concontCell.setCellStyle(cellStyle);

            concontCell = contentRow.createCell(3);
            concontCell.setCellValue(sdf.format(user.getHireDate()));
            concontCell.setCellStyle(cellStyle);

            concontCell = contentRow.createCell(4);
            concontCell.setCellValue(user.getAddress());
            concontCell.setCellStyle(cellStyle);

            rowIndex++;
        }


        //导出数据
        String filename = "带样式的员工数据表.xlsx";

        //设置打开方式和mime类型
        response.setHeader("Content-Disposition", "attachment;filename=" + new String(filename.getBytes(), "ISO8859-1"));
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");

        ServletOutputStream outputStream = response.getOutputStream();
        workbook.write(outputStream);
        workbook.close();
        outputStream.close();
    }

    public void downLoadXlsxByPoiWithTemplate(HttpServletResponse response) throws Exception {


        //1.获取到模板
        String path = ResourceUtils.getURL("classpath:").getPath();
        System.out.println("path===========>" + path);
        File rootFile = new File(path);

        File templateFile = new File(rootFile, "/excel_template/userList.xlsx");

        XSSFWorkbook workbook = new XSSFWorkbook(templateFile);
        //2 查询所有用户数据
        List<User> users = userMapper.selectAll();

        //3.获取模板工作表
        Sheet sheet = workbook.getSheetAt(0);

        //获取准备好的内容单元格的样式, 样式单元格再第二个sheet页 的第一个行第一列

        CellStyle cellStyle = workbook.getSheetAt(1).getRow(0).getCell(0).getCellStyle();

        int rowIndex = 2;
        Row contentRow = null;
        Cell concontCell = null;
        for (User user : users) {
            contentRow = sheet.createRow(rowIndex);
            concontCell = contentRow.createCell(0);
            concontCell.setCellValue(user.getId());
            concontCell.setCellStyle(cellStyle);

            concontCell = contentRow.createCell(1);
            concontCell.setCellValue(user.getUserName());
            concontCell.setCellStyle(cellStyle);

            concontCell = contentRow.createCell(2);
            concontCell.setCellValue(user.getPhone());
            concontCell.setCellStyle(cellStyle);

            concontCell = contentRow.createCell(3);
            concontCell.setCellValue(sdf.format(user.getHireDate()));
            concontCell.setCellStyle(cellStyle);

            concontCell = contentRow.createCell(4);
            concontCell.setCellValue(user.getAddress());
            concontCell.setCellStyle(cellStyle);

            rowIndex++;
        }


        //删除第二个sheet页
        workbook.removeSheetAt(1);

        //导出数据
        String filename = "(用模板导出)带样式的员工数据表.xlsx";

        //设置打开方式和mime类型
        response.setHeader("Content-Disposition", "attachment;filename=" + new String(filename.getBytes(), "ISO8859-1"));
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");

        ServletOutputStream outputStream = response.getOutputStream();
        workbook.write(outputStream);
        workbook.close();
        outputStream.close();


    }

    public void downloadUserInfoByTemplate(Long id, HttpServletResponse response) throws Exception {

        //获取模板信息
        String path = ResourceUtils.getURL("classpath:").getPath();
        File rootFile = new File(path);
        File templateFile = new File(rootFile, "/excel_template/userInfo.xlsx");
        Workbook workbook = new XSSFWorkbook(templateFile);

        //获取到工作表
        Sheet sheet = workbook.getSheetAt(0);

        //获取用户信息
        User user = userMapper.selectByPrimaryKey(id);

        //将用户数据放入模板中
        //用户名 第2行 第2列
        sheet.getRow(1).getCell(1).setCellValue(user.getUserName());

        //手机号 第3行 第2列
        sheet.getRow(2).getCell(1).setCellValue(user.getPhone());

        //生日 第4行 第2列
        sheet.getRow(3).getCell(1).setCellValue(sdf.format(user.getBirthday()));

        //工资 第5行 第2列
        sheet.getRow(4).getCell(1).setCellValue(user.getSalary());

        //入职日期 第6行 第2列
        sheet.getRow(5).getCell(1).setCellValue(sdf.format(user.getHireDate()));

        //省份 第7行 第2列
        sheet.getRow(6).getCell(1).setCellValue(user.getProvince());

        //先住址 第8行 第2列
        sheet.getRow(7).getCell(1).setCellValue(user.getAddress());

        //       司龄  第6行第4列          使用公式稍后处理 =CONCATENATE(DATEDIF(B6,TODAY(),"Y"),"年",DATEDIF(B6,TODAY(),"YM"),"个月")
        sheet.getRow(5).getCell(3).setCellFormula("CONCATENATE(DATEDIF(B6,TODAY(),\"Y\"),\"年\",DATEDIF(B6,TODAY(),\"YM\"),\"个月\")");

        //城市 第7行 第4列
        sheet.getRow(6).getCell(3).setCellValue(user.getCity());

        //图片处理 图片的位置 开始列3 开始行2  结束列4 结束行5
        //==========================开始处理图片

        //先创建一个字节输出流
        ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();

        //读取图片 放入一个带有缓存区的图片类中
        File file = new File(rootFile, user.getPhoto().replace("\\", "/"));
        BufferedImage bufferedImage = ImageIO.read(file);

        //获取图片的后缀名
        String extName = user.getPhoto().substring(user.getPhoto().lastIndexOf(".") + 1).toUpperCase();

        //将图片写入到字节输出流中
        ImageIO.write(bufferedImage, extName, byteArrayOutputStream);


        //Patriarch 控制图片的写入  clientAnchor 指定图片的位置
        Drawing patriarch = sheet.createDrawingPatriarch();

        //指定图片的位置 图片的位置 开始列3 开始行2  结束列4 结束行5
        //前四个单位是: 一个英式公制的单位  1cm = 360000
        //最后连个参数要注意 : 图片的右下角 到指定的单元格的左上角
        ClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, 2, 1, 4, 5);

        //将图片写入到sheet指定的位置

        int format = 0;
        switch (extName) {
            case "JPG": {
                format = XSSFWorkbook.PICTURE_TYPE_JPEG;
            }
            case "JPEG": {
                format = XSSFWorkbook.PICTURE_TYPE_JPEG;
            }
            case "PNG": {
                format = XSSFWorkbook.PICTURE_TYPE_PNG;
            }
        }

        patriarch.createPicture(anchor, workbook.addPicture(byteArrayOutputStream.toByteArray(), format));

        //================================图片处理结束


        //导出数据
        String filename = "员工(" + user.getUserName() + ")个人数据表.xlsx";

        //设置打开方式和mime类型
        response.setHeader("Content-Disposition", "attachment;filename=" + new String(filename.getBytes(), "ISO8859-1"));
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");

        ServletOutputStream outputStream = response.getOutputStream();
        workbook.write(outputStream);
        workbook.close();
        outputStream.close();


    }

    public void downloadUserInfoByTemplate2(Long id, HttpServletResponse response) throws Exception {
        //获取模板信息
        String path = ResourceUtils.getURL("classpath:").getPath();
        File rootFile = new File(path);
        File templateFile = new File(rootFile, "/excel_template/userInfo2.xlsx");
        Workbook workbook = new XSSFWorkbook(templateFile);

        //获取用户信息
        User user = userMapper.selectByPrimaryKey(id);

        String imagePath = path + user.getPhoto().replace("\\", "/");
        ExcelExportEngine.writeToExcel(user, workbook, imagePath);
        //导出数据
        String filename = "员工(" + user.getUserName() + ")个人数据表.xlsx";

        //设置打开方式和mime类型
        response.setHeader("Content-Disposition", "attachment;filename=" + new String(filename.getBytes(), "ISO8859-1"));
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");

        ServletOutputStream outputStream = response.getOutputStream();
        workbook.write(outputStream);
        workbook.close();
        outputStream.close();


    }

    //百万条数据的导出:
    //(1)可定使用高版本的excel
    //(2)使用sax方式解析Excel(XML)
    //(3)限制: 不能使用模板  不能使用太多的样式
    public void downLoadMillion(HttpServletResponse response) throws Exception {
        //1.指定sax方式解析
        Workbook workbook = new SXSSFWorkbook();//sax方式逐行解析
        //Workbook workbook1 = new XSSFWorkbook();//使用dom4j的方式进行解析

        //2.导出的200+w数据 不可能放到同一个sheet中 规定每个sheet不能超过100w
        int page = 1;
        int num = 0;//记录了处理数据的个数
        int rowIndex = 1; //记录每个sheet的行索引
        Row row = null;
        Sheet sheet = null;
        while (true) {
            List<User> userList = this.findPage(page, 100000);
            if (CollectionUtils.isEmpty(userList)) {
                break;
            }

            if (num % 1000000 == 0) {
                sheet = workbook.createSheet("第" + ((num / 1000000) + 1) + "个工作表");
                rowIndex = 1;
                Row titleRow = sheet.createRow(0);
                String[] titles = new String[]{"编号", "姓名", "手机号", "入职日期", "先住址"};
                for (int i = 0; i < titles.length; i++) {
                    titleRow.createCell(i).setCellValue(titles[i]);
                }
            }

            for (User user : userList) {
                log.info("id-->{}", user.getId());
                row = sheet.createRow(rowIndex);
                row.createCell(0).setCellValue(user.getId());
                row.createCell(1).setCellValue(user.getUserName());
                row.createCell(2).setCellValue(user.getPhone());
                row.createCell(3).setCellValue(sdf.format(user.getHireDate()));
                row.createCell(4).setCellValue(user.getAddress());
                rowIndex++;
                num++;
            }
            page++;

        }

        String fileName = "百万用户数数据导出.xlsx";

        //设置打开方式和mime类型
        response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes(), "ISO8859-1"));
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");

        ServletOutputStream outputStream = response.getOutputStream();
        workbook.write(outputStream);
        workbook.close();
        outputStream.close();

    }

    //使用csv文件导出百万数据
    public void downLoadCSV(HttpServletResponse response) throws Exception {
        ServletOutputStream outputStream = response.getOutputStream();
        String fileName = "使用csv文件导出百万数据.csv";
        //设置打开方式和mime类型
        response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes(), "ISO8859-1"));
        response.setContentType("text/csv");
        CSVWriter csvWriter = new CSVWriter(new OutputStreamWriter(outputStream, "utf-8"));

        //写入小标题
        String[] titles = new String[]{"编号", "姓名", "手机号", "入职日期", "先住址"};

        csvWriter.writeNext(titles);
        int page = 1;
        while (true) {
            List<User> userList = this.findPage(page, 200000);
            if (org.apache.commons.collections.CollectionUtils.isEmpty(userList)) {
                break;
            }
            for (User user : userList) {
                csvWriter.writeNext(new String[]{user.getId() + "",
                        user.getUserName(),
                        user.getPhone(),
                        sdf.format(user.getHireDate()),
                        user.getAddress()
                });
            }
            page++;
            csvWriter.flush();
        }
        csvWriter.close();


    }

    public User findUserInfo(Long id) {
        //查询用户信息
        User userInfo = userMapper.selectByPrimaryKey(id);
        Resource resource = new Resource();
        resource.setUserId(userInfo.getId());

        //查询用户所关联的资源信息
        List<Resource> resources = resourceMapper.select(resource);
        userInfo.setResourceList(resources);
        return userInfo;
    }

    public void downloadContract(Long id, HttpServletResponse response) throws Exception {
        //1. 读取模板
        File rootFile = new File(ResourceUtils.getURL("classpath:").getPath());//获取项目的根目录
        File templateFile = new File(rootFile, "/word_template/contract_template.docx");
        XWPFDocument word = new XWPFDocument(new FileInputStream(templateFile));


        //2.查询当前用户user - > map
        User userInfo = this.findUserInfo(id);

        Map<String, String> params = new HashMap<>();
        params.put("userName", userInfo.getUserName());
        params.put("hireDate", sdf.format(userInfo.getHireDate()));
        params.put("address", userInfo.getAddress());

        //3.替换数据
        //开始处理正文
        List<XWPFParagraph> paragraphs = word.getParagraphs();
        for (XWPFParagraph paragraph : paragraphs) {
            List<XWPFRun> runs = paragraph.getRuns();
            for (XWPFRun run : runs) {
                String text = run.getText(0);
                for (String key : params.keySet()) {
                    if (text.contains(key)) {
                        run.setText(text.replaceAll(key, params.get(key)), 0);
                    }
                }
            }
        }

        //正文处理结束

        //开始处理表格
        //名称 价值  是否需要归还  招聘
        List<Resource> resourceList = userInfo.getResourceList();
        XWPFTable xwpfTable = word.getTables().get(0);//只有一个表格
        XWPFTableRow row = xwpfTable.getRow(0);
        int rowIndex = 1;

        for (Resource resource : resourceList) {
            copyRow(xwpfTable, row, rowIndex);
            XWPFTableRow row1 = xwpfTable.getRow(rowIndex);
            row1.getCell(0).setText(resource.getName());
            row1.getCell(1).setText(resource.getPrice().toString());
            row1.getCell(2).setText(resource.getNeedReturn() ? "需要" : "不需要");
            String photoPath = resource.getPhoto();
            if (StringUtils.isNotBlank(photoPath)) {
                photoPath = resource.getPhoto().replace("\\", "/");
            }
            File imageFile = new File(rootFile, "/static" + photoPath);
            setCellImage(row1.getCell(3), imageFile);
            //row1.getCell(3).setText(file.getPath());
            rowIndex++;
        }

        String filename = "员工(" + userInfo.getUserName() + ")合同.docx";

        response.setHeader("Content-Disposition", "attachment;filename=" + new String(filename.getBytes(), "ISO8859-1"));
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        ServletOutputStream outputStream = response.getOutputStream();
        word.write(outputStream);
        outputStream.flush();
        outputStream.close();


    }

    //向单元格中设置图片
    private void setCellImage(XWPFTableCell cell, File imageFile) {
        XWPFRun run = cell.getParagraphs().get(0).createRun();
        try (FileInputStream fileInputStream = new FileInputStream(imageFile)) {
            //InputStream pictureData, int pictureType, String filename, int width, int height
            run.addPicture(fileInputStream, XWPFDocument.PICTURE_TYPE_JPEG, imageFile.getName(), Units.toEMU(100), Units.toEMU(50));
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    private void copyRow(XWPFTable xwpfTable, XWPFTableRow sourceRow, int rowIndex) {
        XWPFTableRow tagetRow = xwpfTable.insertNewTableRow(rowIndex);
        tagetRow.getCtRow().setTrPr(sourceRow.getCtRow().getTrPr());
        //获取源行的单元格
        List<XWPFTableCell> cells = sourceRow.getTableCells();
        if (CollectionUtils.isEmpty(cells)) {
            return;
        }

        for (XWPFTableCell tableCell : cells) {
            XWPFTableCell targetCell = tagetRow.addNewTableCell();
            //附上单元格的样式和单元格的属性
            targetCell.getCTTc().setTcPr(tableCell.getCTTc().getTcPr());
            targetCell.getParagraphs().get(0).getCTP().setPPr(tableCell.getParagraphs().get(0).getCTP().getPPr());
        }

    }

    public void downLoadWithEasyPOI(HttpServletResponse response) throws Exception {

        String filename = "员工信息.xlsx";

        response.setHeader("Content-Disposition", "attachment;filename=" + new String(filename.getBytes(), "ISO8859-1"));
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        ServletOutputStream outputStream = response.getOutputStream();
        List<User> userList = userMapper.selectAll();
        ExportParams params = new ExportParams("员工信息表", "员工信息sheet", ExcelType.XSSF);
        Workbook workbook = ExcelExportUtil.exportExcel(params, User.class, userList);
        workbook.write(outputStream);
        outputStream.close();

    }

    public void uploadExcelByEasyPOI(MultipartFile file) throws Exception {

        ImportParams params = new ImportParams();
        params.setNeedSave(false);
        params.setTitleRows(1);//todo 需要注意
        params.setHeadRows(1);

        List<User> userList = ExcelImportUtil.importExcel(file.getInputStream(), User.class, params);
        for (User user : userList) {
            user.setId(null);
            userMapper.insert(user);
        }

    }

    public void downloadUserInfoByTemplate3(Long id, HttpServletResponse response) throws Exception {

        //1. 读取模板
        File rootFile = new File(ResourceUtils.getURL("classpath:").getPath());//获取项目的根目录
        File templateFile = new File(rootFile, "/excel_template/userInfo3.xlsx");

        User userInfo = this.findUserInfo(id);


        TemplateExportParams params = new TemplateExportParams(templateFile.getPath(), true);


        //2.查询当前用户user - > map
        Map<String, Object> map = new HashMap<>();

        map.put("userName", userInfo.getUserName());
        map.put("phone", userInfo.getPhone());
        map.put("address", userInfo.getAddress());
        map.put("birthday", sdf.format(userInfo.getBirthday()));
        map.put("salary", userInfo.getSalary());
        map.put("hireDate", sdf.format(userInfo.getHireDate()));
        map.put("province", userInfo.getProvince());
        map.put("city", userInfo.getCity());
        ImageEntity imageEntity = new ImageEntity();
        imageEntity.setUrl(userInfo.getPhoto());
        imageEntity.setColspan(2);//占用多少列
        imageEntity.setRowspan(4);//占用多少行

        map.put("photo", imageEntity);

        Workbook workbook = ExcelExportUtil.exportExcel(params, map);

        String filename = "员工(" + userInfo.getUserName() + ")信息.xlsx";
        response.setHeader("Content-Disposition", "attachment;filename=" + new String(filename.getBytes(), "ISO8859-1"));
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        ServletOutputStream outputStream = response.getOutputStream();
        workbook.write(outputStream);
        outputStream.close();
    }

    public void downLoadCSVByEasyPIO(HttpServletResponse response) throws Exception {

        ServletOutputStream outputStream = response.getOutputStream();
        String filename = "百万数据的导出.csv";
        response.setHeader("content-disposition", "attachment;filename=" + new String(filename.getBytes(), "ISO8859-1"));
        response.setContentType("text/csv");
        List<User> all = this.findAll();
        CsvExportParams params = new CsvExportParams();
        params.setExclusions(new String[]{"照片"});//排除照片
        CsvExportUtil.exportCsv(params, User.class, all, outputStream);


    }

    public void downloadContractByEasyPOI(Long id, HttpServletResponse response) throws Exception {
        File rootPath = new File(ResourceUtils.getURL("classpath:").getPath());
        File templateFile = new File(rootPath, "/word_template/contract_template2.docx");
        //        先获取导出word需要的数据
        User user = this.findUserInfo(id);
        //        把需要的数据放到map中，方便替换
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("userName", user.getUserName());
        params.put("hireDate", sdf.format(user.getHireDate()));
        params.put("address", user.getAddress());

        //        下面是表格中需要的数据
        List<Map> maplist = new ArrayList<>();
        Map<String, Object> map = null;
        for (Resource resource : user.getResourceList()) {
            map = new HashMap<String, Object>();
            map.put("name", resource.getName());
            map.put("price", resource.getPrice());
            map.put("needReturn", resource.getNeedReturn());
            ImageEntity image = new ImageEntity();
            image.setHeight(180);
            image.setWidth(240);
            image.setUrl(rootPath.getPath() + "\\static" + resource.getPhoto());
            map.put("photo", image);
            maplist.add(map);
        }
        //        把组建好的表格需要的数据放到大map中
        params.put("maplist", maplist);
        //        根据模板+数据 导出文档
        XWPFDocument xwpfDocument = WordExportUtil.exportWord07(templateFile.getPath(), params);
        String filename = user.getUserName() + "_合同.docx";
        //            设置文件的打开方式和mime类型
        ServletOutputStream outputStream = response.getOutputStream();
        response.setHeader("Content-Disposition", "attachment;filename=" + new String(filename.getBytes(), "ISO8859-1"));
        response.setContentType("application/vnd.openxmlformats-officedocument.wordprocessingml.document");
        xwpfDocument.write(outputStream);

    }
}



















